﻿using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;

namespace iTool_Cetector
{
    public class CheckDatabases
    {
        string dbName { get; set; }
        string dbType { get; set; }
        string masterConnaction { get; set; }
        string connaction { get; set; }
        public CheckDatabases(int port, string pwd, string dbType, string ip = "127.0.0.1")
        {
            //this.dbName 106.14.45.63,2533
            this.dbType = dbType;
            switch (dbType)
            {
                case "iToolBase":
                    this.dbName = "iTool.CloudBase";
                    break;
                case "Logger":
                    this.dbName = "iTool.Logger";
                    break;
                case "Function":
                    this.dbName = "iTool.Function";
                    break;
                case "Message":
                    this.dbName = "iTool.Message";
                    break;
                default:
                    this.dbName = dbType;
                    break;
            }
            this.masterConnaction = $"Data Source={ip},{port};database=master;uid=sa;pwd={pwd};MultipleActiveResultSets=true;";
            this.connaction = $"Data Source={ip},{port};database={dbName};uid=sa;pwd={pwd};MultipleActiveResultSets=true;";
        }

        private async Task<bool> CheckDatabaseExists()
        {
            await using SqlConnection conn = new SqlConnection(this.masterConnaction);
            await using SqlCommand command = new SqlCommand($"SELECT database_id from sys.databases WHERE Name  = '{dbName}'", conn);
            await conn.OpenAsync();

            object resultObj = await command.ExecuteScalarAsync();

            int databaseID = 0;

            if (resultObj != null)
            {
                int.TryParse(resultObj.ToString(), out databaseID);
            }

            return databaseID > 0;
        }

        public async Task CreateDatabaseStructer()
        {

            if (string.IsNullOrWhiteSpace(this.dbName))
            {
                return;
            }

            if (!await this.CheckDatabaseExists())
            {
                // 创建DB
                await using (SqlConnection conn = new SqlConnection(this.masterConnaction))
                {
                    await using SqlCommand command = new SqlCommand($@"CREATE DATABASE [{dbName}]", conn);
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                }

                string builderScript = string.Empty;

                switch (this.dbType)
                {
                    case "iToolBase":
                        builderScript = CloudBaseSQLBuilder().ToString();
                        break;
                    case "Logger":
                        builderScript = LoggerSQLBuilder().ToString();
                        break;
                    case "Function":
                        builderScript = FunctionSQLBuilder().ToString();
                        break;
                    case "Message":
                        builderScript = MessageSQLBuilder().ToString();
                        break;
                    default:
                        return;
                }

                // 创建结构
                await using (SqlConnection conn = new SqlConnection(this.connaction))
                {
                    await using SqlCommand command = new SqlCommand(builderScript, conn);
                    await conn.OpenAsync();
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        private StringBuilder LoggerSQLBuilder() 
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine($@"ALTER DATABASE [{dbName}] SET READ_COMMITTED_SNAPSHOT ON; 
                                ALTER DATABASE [{dbName}] SET ALLOW_SNAPSHOT_ISOLATION ON;");

            builder.AppendLine(@"
                         -- 用于消息存储
                           CREATE TABLE [dbo].[LogClientException](
	                        [id] [int] IDENTITY(1,1) NOT NULL,
	                        [level] [varchar](20) NULL,
	                        [category] [varchar](200) NULL,
	                        [message] [nvarchar](max) NULL,
	                        [exception] [nvarchar](max) NULL,
	                        [createTime] [datetime] NOT NULL
                        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

                            CREATE TABLE [dbo].[LogCustomException](
	                            [id] [int] IDENTITY(1,1) NOT NULL,
	                            [level] [varchar](20) NULL,
	                            [category] [varchar](200) NULL,
	                            [message] [nvarchar](max) NULL,
	                            [exception] [nvarchar](max) NULL,
	                            [createTime] [datetime] NOT NULL
                            ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

						CREATE TABLE [dbo].[LogFunction](
							[id] [int] IDENTITY(1,1) NOT NULL,
							[host] [varchar](40) NULL,
							[port] [int] NULL,
							[session] [nvarchar](600) NULL,
							[channel] [nvarchar](60) NULL,
							[parameter] [nvarchar](600) NULL,
							[requestBody] [nvarchar](600) NULL,
							[requestModuleName] [nvarchar](600) NULL,
							[requestFunctionName] [nvarchar](600) NULL,
							[responseBody] [nvarchar](max) NULL,
							[responseState] [int] NOT NULL,
							[runTimeTotalMilliseconds] [float] NULL,
							[isUseState] [bit] NOT NULL,
							[createTime] [datetime] NOT NULL
						) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

						CREATE TABLE [dbo].[LogGateway](
							[ID] [int] IDENTITY(1,1) NOT NULL,
							[RequestIp] [varchar](15) NULL,
							[RequestDNS] [varchar](15) NULL,
							[RequestUrl] [varchar](25) NULL,
							[RequestForUrl] [varchar](25) NULL,
							[methodType] [varchar](25) NULL,
							[Createtime] [datetime] NULL,
						 CONSTRAINT [PK_LogGateWay] PRIMARY KEY CLUSTERED 
						(
							[ID] ASC
						)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
						) ON [PRIMARY]

						CREATE TABLE [dbo].[LogSystemException](
							[id] [int] IDENTITY(1,1) NOT NULL,
							[level] [varchar](20) NULL,
							[category] [varchar](200) NULL,
							[message] [nvarchar](max) NULL,
							[exception] [nvarchar](max) NULL,
							[createTime] [datetime] NOT NULL
						) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

						CREATE TABLE [dbo].[LogTracking](
							[Id] [int] IDENTITY(1,1) NOT NULL,
							[Token] [varchar](50) NOT NULL,
							[Action] [varchar](50) NOT NULL,
							[message] [nvarchar](max) NULL,
							[createtime] [datetime] NULL,
							[starttime] [datetime] NULL,
						 CONSTRAINT [PK_LogTracking] PRIMARY KEY CLUSTERED 
						(
							[Id] ASC
						)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
						) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

                      ");

            return builder;
        }

        private StringBuilder FunctionSQLBuilder()
        {
            StringBuilder builder = new StringBuilder();

            builder.AppendLine($@"ALTER DATABASE [{dbName}] SET READ_COMMITTED_SNAPSHOT ON; 
                                ALTER DATABASE [{dbName}] SET ALLOW_SNAPSHOT_ISOLATION ON;");

            builder.AppendLine(@"
	                        -- 用于云函数基础信息存储
                            create table [Function]
                            (
                                id int identity(1,1) primary key,
		                        isUseState bit,
                                moduleName nvarchar(100) not null,
                                modulePath nvarchar(100) not null,
                                scriptContent nvarchar(max) not null
                            )

                            -- 用于存储Node依赖环境
                            create table [NodePackage]
                            (
                                id int identity(1,1) primary key,
                                drName varchar(100),
                                packageName varchar(100),
                                version varchar(20),
                                isInstallComplete bit,
                                reTryCount int,
                                lastReTryTime datetime
                            )
                        ");

            builder.AppendLine(@";INSERT INTO [Function]([isUseState],[moduleName],[modulePath],[scriptContent]) VALUES (0,'AppSetting','./CloudFunctions/AppSetting','module.exports = {}');
INSERT INTO [Function]([isUseState],[moduleName],[modulePath],[scriptContent]) VALUES (0,'Global','./CloudFunctions/Global','module.exports = {}');");

            return builder;
        }

        private StringBuilder MessageSQLBuilder()
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine($@"ALTER DATABASE [{dbName}] SET READ_COMMITTED_SNAPSHOT ON; 
                                ALTER DATABASE [{dbName}] SET ALLOW_SNAPSHOT_ISOLATION ON;");

            builder.AppendLine(@"
	                        -- 用于消息存储
                            create table [NotifyMessage]
                            (
                                id int identity(1,1) primary key,
                                sendChannel varchar(20),
                                reciveChannel varchar(20),
                                content nvarchar(4000),
                                [status] int default 0,
                                createTime datetime
                            )

	                        CREATE NONCLUSTERED INDEX [ClusteredIndex-CreateTimeAndChannel] ON [NotifyMessage]
	                        (
		                        [reciveChannel] ASC,
		                        [createTime] DESC
	                        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

	                        CREATE NONCLUSTERED INDEX [NonClusteredIndex-20200914-222901] ON [NotifyMessage]
	                        (
		                        [reciveChannel] ASC,
		                        [sendChannel] ASC,
		                        [createTime] ASC
	                        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                        ");

            return builder;
        }

        private StringBuilder  CloudBaseSQLBuilder()
        {
            StringBuilder builder = new StringBuilder();

            builder.AppendLine($@"ALTER DATABASE [{dbName}] SET READ_COMMITTED_SNAPSHOT ON; 
                                ALTER DATABASE [{dbName}] SET ALLOW_SNAPSHOT_ISOLATION ON;");

            builder.AppendLine(@"CREATE TABLE OrleansQuery
                            (
	                            QueryKey VARCHAR(64) NOT NULL,
	                            QueryText VARCHAR(8000) NOT NULL,
	                            CONSTRAINT OrleansQuery_Key PRIMARY KEY(QueryKey)
                            );

                            CREATE TABLE Storage
                            (
                                GrainIdHash                INT NOT NULL,
                                GrainIdN0                BIGINT NOT NULL,
                                GrainIdN1                BIGINT NOT NULL,
                                GrainTypeHash            INT NOT NULL,
                                GrainTypeString            NVARCHAR(512) NOT NULL,
                                GrainIdExtensionString    NVARCHAR(512) NULL,
                                ServiceId                NVARCHAR(150) NOT NULL,
                                PayloadBinary    VARBINARY(MAX) NULL,
                                PayloadXml        XML NULL,
                                PayloadJson        NVARCHAR(MAX) NULL,
                                ModifiedOn DATETIME2(3) NOT NULL,
                                Version INT NULL
                            );

                            CREATE NONCLUSTERED INDEX IX_Storage ON Storage(GrainIdHash, GrainTypeHash);

                            ALTER TABLE Storage SET(LOCK_ESCALATION = DISABLE);

                            IF EXISTS (SELECT 1 FROM sys.dm_db_persisted_sku_features WHERE feature_id = 100)
                            BEGIN
                                ALTER TABLE Storage REBUILD PARTITION = ALL WITH(DATA_COMPRESSION = PAGE);
                            END");

            builder.AppendLine(@"
                            ;INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
                                'WriteToStorageKey',
                                '-- When Orleans is running in normal, non-split state, there will
                                -- be only one grain with the given ID and type combination only. This
                                -- grain saves states mostly serially if Orleans guarantees are upheld. Even
                                -- if not, the updates should work correctly due to version number.
                                --
                                -- In split brain situations there can be a situation where there are two or more
                                -- grains with the given ID and type combination. When they try to INSERT
                                -- concurrently, the table needs to be locked pessimistically before one of
                                -- the grains gets @GrainStateVersion = 1 in return and the other grains will fail
                                -- to update storage. The following arrangement is made to reduce locking in normal operation.
                                --
                                -- If the version number explicitly returned is still the same, Orleans interprets it so the update did not succeed
                                -- and throws an InconsistentStateException.
                                --
                                -- See further information at https://dotnet.github.io/orleans/Documentation/Core-Features/Grain-Persistence.html.
                                BEGIN TRANSACTION;
                                SET XACT_ABORT, NOCOUNT ON;

                                DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;


                                -- If the @GrainStateVersion is not zero, this branch assumes it exists in this database.
                                -- The NULL value is supplied by Orleans when the state is new.
                                IF @GrainStateVersion IS NOT NULL
                                BEGIN
                                    UPDATE Storage
                                    SET
                                        PayloadBinary = @PayloadBinary,
                                        PayloadJson = @PayloadJson,
                                        PayloadXml = @PayloadXml,
                                        ModifiedOn = GETUTCDATE(),
                                        Version = Version + 1,
                                        @NewGrainStateVersion = Version + 1,
                                        @GrainStateVersion = Version + 1
                                    WHERE
                                        GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
                                        AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
                                        AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
                                        AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
                                        AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
                                        AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
                                        AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
                                        AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
                                        OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));
                                END

                                -- The grain state has not been read. The following locks rather pessimistically
                                -- to ensure only one INSERT succeeds.
                                IF @GrainStateVersion IS NULL
                                BEGIN
                                    INSERT INTO Storage
                                    (
                                        GrainIdHash,
                                        GrainIdN0,
                                        GrainIdN1,
                                        GrainTypeHash,
                                        GrainTypeString,
                                        GrainIdExtensionString,
                                        ServiceId,
                                        PayloadBinary,
                                        PayloadJson,
                                        PayloadXml,
                                        ModifiedOn,
                                        Version
                                    )
                                    SELECT
                                        @GrainIdHash,
                                        @GrainIdN0,
                                        @GrainIdN1,
                                        @GrainTypeHash,
                                        @GrainTypeString,
                                        @GrainIdExtensionString,
                                        @ServiceId,
                                        @PayloadBinary,
                                        @PayloadJson,
                                        @PayloadXml,
                                        GETUTCDATE(),
                                        1
                                     WHERE NOT EXISTS
                                     (
                                        -- There should not be any version of this grain state.
                                        SELECT 1
                                        FROM Storage WITH(XLOCK, ROWLOCK, HOLDLOCK, INDEX(IX_Storage))
                                        WHERE
                                            GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
                                            AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
                                            AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
                                            AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
                                            AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
                                            AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
                                            AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
                                     ) OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));

                                    IF @@ROWCOUNT > 0
                                    BEGIN
                                        SET @NewGrainStateVersion = 1;
                                    END
                                END

                                SELECT @NewGrainStateVersion AS NewGrainStateVersion;
                                COMMIT TRANSACTION;'
                            );

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
                                'ClearStorageKey',
                                'BEGIN TRANSACTION;
                                SET XACT_ABORT, NOCOUNT ON;
                                DECLARE @NewGrainStateVersion AS INT = @GrainStateVersion;
                                UPDATE Storage
                                SET
                                    PayloadBinary = NULL,
                                    PayloadJson = NULL,
                                    PayloadXml = NULL,
                                    ModifiedOn = GETUTCDATE(),
                                    Version = Version + 1,
                                    @NewGrainStateVersion = Version + 1
                                WHERE
                                    GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
                                    AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
                                    AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
                                    AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
                                    AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
                                    AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
                                    AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
                                    AND Version IS NOT NULL AND Version = @GrainStateVersion AND @GrainStateVersion IS NOT NULL
                                    OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));

                                SELECT @NewGrainStateVersion;
                                COMMIT TRANSACTION;'
                            );

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
                                'ReadFromStorageKey',
                                '-- The application code will deserialize the relevant result. Not that the query optimizer
                                -- estimates the result of rows based on its knowledge on the index. It does not know there
                                -- will be only one row returned. Forcing the optimizer to process the first found row quickly
                                -- creates an estimate for a one-row result and makes a difference on multi-million row tables.
                                -- Also the optimizer is instructed to always use the same plan via index using the OPTIMIZE
                                -- FOR UNKNOWN flags. These hints are only available in SQL Server 2008 and later. They
                                -- should guarantee the execution time is robustly basically the same from query-to-query.
                                SELECT
                                    PayloadBinary,
                                    PayloadXml,
                                    PayloadJson,
                                    Version
                                FROM
                                    Storage
                                WHERE
                                    GrainIdHash = @GrainIdHash AND @GrainIdHash IS NOT NULL
                                    AND GrainTypeHash = @GrainTypeHash AND @GrainTypeHash IS NOT NULL
                                    AND (GrainIdN0 = @GrainIdN0 OR @GrainIdN0 IS NULL)
                                    AND (GrainIdN1 = @GrainIdN1 OR @GrainIdN1 IS NULL)
                                    AND (GrainTypeString = @GrainTypeString OR @GrainTypeString IS NULL)
                                    AND ((@GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString IS NOT NULL AND GrainIdExtensionString = @GrainIdExtensionString) OR @GrainIdExtensionString IS NULL AND GrainIdExtensionString IS NULL)
                                    AND ServiceId = @ServiceId AND @ServiceId IS NOT NULL
                                    OPTION(FAST 1, OPTIMIZE FOR(@GrainIdHash UNKNOWN, @GrainTypeHash UNKNOWN));'
                            );

                            -- For each deployment, there will be only one (active) membership version table version column which will be updated periodically.
                            CREATE TABLE OrleansMembershipVersionTable
                            (
	                            DeploymentId NVARCHAR(150) NOT NULL,
	                            Timestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(),
	                            Version INT NOT NULL DEFAULT 0,

	                            CONSTRAINT PK_OrleansMembershipVersionTable_DeploymentId PRIMARY KEY(DeploymentId)
                            );

                            -- Every silo instance has a row in the membership table.
                            CREATE TABLE OrleansMembershipTable
                            (
	                            DeploymentId NVARCHAR(150) NOT NULL,
	                            Address VARCHAR(45) NOT NULL,
	                            Port INT NOT NULL,
	                            Generation INT NOT NULL,
	                            SiloName NVARCHAR(150) NOT NULL,
	                            HostName NVARCHAR(150) NOT NULL,
	                            Status INT NOT NULL,
	                            ProxyPort INT NULL,
	                            SuspectTimes VARCHAR(8000) NULL,
	                            StartTime DATETIME2(3) NOT NULL,
	                            IAmAliveTime DATETIME2(3) NOT NULL,

	                            CONSTRAINT PK_MembershipTable_DeploymentId PRIMARY KEY(DeploymentId, Address, Port, Generation),
	                            CONSTRAINT FK_MembershipTable_MembershipVersionTable_DeploymentId FOREIGN KEY (DeploymentId) REFERENCES OrleansMembershipVersionTable (DeploymentId)
                            );

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'UpdateIAmAlivetimeKey','
	                            -- This is expected to never fail by Orleans, so return value
	                            -- is not needed nor is it checked.
	                            SET NOCOUNT ON;
	                            UPDATE OrleansMembershipTable
	                            SET
		                            IAmAliveTime = @IAmAliveTime
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
		                            AND Address = @Address AND @Address IS NOT NULL
		                            AND Port = @Port AND @Port IS NOT NULL
		                            AND Generation = @Generation AND @Generation IS NOT NULL;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'InsertMembershipVersionKey','
	                            SET NOCOUNT ON;
	                            INSERT INTO OrleansMembershipVersionTable
	                            (
		                            DeploymentId
	                            )
	                            SELECT @DeploymentId
	                            WHERE NOT EXISTS
	                            (
	                            SELECT 1
	                            FROM
		                            OrleansMembershipVersionTable
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
	                            );

	                            SELECT @@ROWCOUNT;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'InsertMembershipKey','
	                            SET XACT_ABORT, NOCOUNT ON;
	                            DECLARE @ROWCOUNT AS INT;
	                            BEGIN TRANSACTION;
	                            INSERT INTO OrleansMembershipTable
	                            (
		                            DeploymentId,
		                            Address,
		                            Port,
		                            Generation,
		                            SiloName,
		                            HostName,
		                            Status,
		                            ProxyPort,
		                            StartTime,
		                            IAmAliveTime
	                            )
	                            SELECT
		                            @DeploymentId,
		                            @Address,
		                            @Port,
		                            @Generation,
		                            @SiloName,
		                            @HostName,
		                            @Status,
		                            @ProxyPort,
		                            @StartTime,
		                            @IAmAliveTime
	                            WHERE NOT EXISTS
	                            (
	                            SELECT 1
	                            FROM
		                            OrleansMembershipTable
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
		                            AND Address = @Address AND @Address IS NOT NULL
		                            AND Port = @Port AND @Port IS NOT NULL
		                            AND Generation = @Generation AND @Generation IS NOT NULL
	                            );

	                            UPDATE OrleansMembershipVersionTable
	                            SET
		                            Timestamp = GETUTCDATE(),
		                            Version = Version + 1
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
		                            AND Version = @Version AND @Version IS NOT NULL
		                            AND @@ROWCOUNT > 0;

	                            SET @ROWCOUNT = @@ROWCOUNT;

	                            IF @ROWCOUNT = 0
		                            ROLLBACK TRANSACTION
	                            ELSE
		                            COMMIT TRANSACTION
	                            SELECT @ROWCOUNT;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'UpdateMembershipKey','
	                            SET XACT_ABORT, NOCOUNT ON;
	                            BEGIN TRANSACTION;

	                            UPDATE OrleansMembershipVersionTable
	                            SET
		                            Timestamp = GETUTCDATE(),
		                            Version = Version + 1
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
		                            AND Version = @Version AND @Version IS NOT NULL;

	                            UPDATE OrleansMembershipTable
	                            SET
		                            Status = @Status,
		                            SuspectTimes = @SuspectTimes,
		                            IAmAliveTime = @IAmAliveTime
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
		                            AND Address = @Address AND @Address IS NOT NULL
		                            AND Port = @Port AND @Port IS NOT NULL
		                            AND Generation = @Generation AND @Generation IS NOT NULL
		                            AND @@ROWCOUNT > 0;

	                            SELECT @@ROWCOUNT;
	                            COMMIT TRANSACTION;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'GatewaysQueryKey','
	                            SELECT
		                            Address,
		                            ProxyPort,
		                            Generation
	                            FROM
		                            OrleansMembershipTable
	                            WHERE
		                            DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL
		                            AND Status = @Status AND @Status IS NOT NULL
		                            AND ProxyPort > 0;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'MembershipReadRowKey','
	                            SELECT
		                            v.DeploymentId,
		                            m.Address,
		                            m.Port,
		                            m.Generation,
		                            m.SiloName,
		                            m.HostName,
		                            m.Status,
		                            m.ProxyPort,
		                            m.SuspectTimes,
		                            m.StartTime,
		                            m.IAmAliveTime,
		                            v.Version
	                            FROM
		                            OrleansMembershipVersionTable v
		                            -- This ensures the version table will returned even if there is no matching membership row.
		                            LEFT OUTER JOIN OrleansMembershipTable m ON v.DeploymentId = m.DeploymentId
		                            AND Address = @Address AND @Address IS NOT NULL
		                            AND Port = @Port AND @Port IS NOT NULL
		                            AND Generation = @Generation AND @Generation IS NOT NULL
	                            WHERE
		                            v.DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'MembershipReadAllKey','
	                            SELECT
		                            v.DeploymentId,
		                            m.Address,
		                            m.Port,
		                            m.Generation,
		                            m.SiloName,
		                            m.HostName,
		                            m.Status,
		                            m.ProxyPort,
		                            m.SuspectTimes,
		                            m.StartTime,
		                            m.IAmAliveTime,
		                            v.Version
	                            FROM
		                            OrleansMembershipVersionTable v LEFT OUTER JOIN OrleansMembershipTable m
		                            ON v.DeploymentId = m.DeploymentId
	                            WHERE
		                            v.DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'DeleteMembershipTableEntriesKey','
	                            DELETE FROM OrleansMembershipTable
	                            WHERE DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
	                            DELETE FROM OrleansMembershipVersionTable
	                            WHERE DeploymentId = @DeploymentId AND @DeploymentId IS NOT NULL;
                            ');


                            -- Orleans Reminders table - https://dotnet.github.io/orleans/Documentation/Core-Features/Timers-and-Reminders.html
                            CREATE TABLE OrleansRemindersTable
                            (
	                            ServiceId NVARCHAR(150) NOT NULL,
	                            GrainId VARCHAR(150) NOT NULL,
	                            ReminderName NVARCHAR(150) NOT NULL,
	                            StartTime DATETIME2(3) NOT NULL,
	                            Period BIGINT NOT NULL,
	                            GrainHash INT NOT NULL,
	                            Version INT NOT NULL,

	                            CONSTRAINT PK_RemindersTable_ServiceId_GrainId_ReminderName PRIMARY KEY(ServiceId, GrainId, ReminderName)
                            );

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'UpsertReminderRowKey','
	                            DECLARE @Version AS INT = 0;
	                            SET XACT_ABORT, NOCOUNT ON;
	                            BEGIN TRANSACTION;
	                            UPDATE OrleansRemindersTable WITH(UPDLOCK, ROWLOCK, HOLDLOCK)
	                            SET
		                            StartTime = @StartTime,
		                            Period = @Period,
		                            GrainHash = @GrainHash,
		                            @Version = Version = Version + 1
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL
		                            AND GrainId = @GrainId AND @GrainId IS NOT NULL
		                            AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL;
	                            INSERT INTO OrleansRemindersTable
	                            (
		                            ServiceId,
		                            GrainId,
		                            ReminderName,
		                            StartTime,
		                            Period,
		                            GrainHash,
		                            Version
	                            )
	                            SELECT
		                            @ServiceId,
		                            @GrainId,
		                            @ReminderName,
		                            @StartTime,
		                            @Period,
		                            @GrainHash,
		                            0
	                            WHERE
		                            @@ROWCOUNT=0;
	                            SELECT @Version AS Version;
	                            COMMIT TRANSACTION;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'ReadReminderRowsKey','
	                            SELECT
		                            GrainId,
		                            ReminderName,
		                            StartTime,
		                            Period,
		                            Version
	                            FROM OrleansRemindersTable
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL
		                            AND GrainId = @GrainId AND @GrainId IS NOT NULL;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'ReadReminderRowKey','
	                            SELECT
		                            GrainId,
		                            ReminderName,
		                            StartTime,
		                            Period,
		                            Version
	                            FROM OrleansRemindersTable
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL
		                            AND GrainId = @GrainId AND @GrainId IS NOT NULL
		                            AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'ReadRangeRows1Key','
	                            SELECT
		                            GrainId,
		                            ReminderName,
		                            StartTime,
		                            Period,
		                            Version
	                            FROM OrleansRemindersTable
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL
		                            AND GrainHash > @BeginHash AND @BeginHash IS NOT NULL
		                            AND GrainHash <= @EndHash AND @EndHash IS NOT NULL;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'ReadRangeRows2Key','
	                            SELECT
		                            GrainId,
		                            ReminderName,
		                            StartTime,
		                            Period,
		                            Version
	                            FROM OrleansRemindersTable
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL
		                            AND ((GrainHash > @BeginHash AND @BeginHash IS NOT NULL)
		                            OR (GrainHash <= @EndHash AND @EndHash IS NOT NULL));
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'DeleteReminderRowKey','
	                            DELETE FROM OrleansRemindersTable
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL
		                            AND GrainId = @GrainId AND @GrainId IS NOT NULL
		                            AND ReminderName = @ReminderName AND @ReminderName IS NOT NULL
		                            AND Version = @Version AND @Version IS NOT NULL;
	                            SELECT @@ROWCOUNT;
                            ');

                            INSERT INTO OrleansQuery(QueryKey, QueryText)
                            VALUES
                            (
	                            'DeleteReminderRowsKey','
	                            DELETE FROM OrleansRemindersTable
	                            WHERE
		                            ServiceId = @ServiceId AND @ServiceId IS NOT NULL;
                            ');");

            return builder;
        }
    }
}
